SnowPro Advanced Data Engineer v1.0
Question 1
The following chart represents the performance of a virtual warehouse over time:
A Data Engineer notices that the warehouse is queueing queries. The warehouse is size X-Small, the minimum and maximum cluster counts are set to 1, the scaling policy is set to standard, and auto-suspend is set to 10 minutes.
How can the performance be improved?
- A. Change the cluster settings.
- B. Increase the size of the warehouse.
- C. Change the scaling policy to economy.
- D. Change auto-suspend to a longer time frame.
Answer : A
Question 2
A secure function returns data coming through an inbound share.
What will happen if a Data Engineer tries to assign USAGE privileges on this function to an outbound share?
- A. An error will be returned because the Engineer cannot share data that has already been shared.
- B. An error will be returned because only views and secure stored procedures can be shared.
- C. An error will be returned because only secure functions can be shared with inbound shares.
- D. The Engineer will be able to share the secure function with other accounts.
Answer : A
Question 3
Which functions will compute a 'fingerprint' over an entire table, query result, or window to quickly detect changes to table contents or query results? (Choose two.)
- A. HASH(*)
- B. HASH_AGG(*)
- C. HASH_AGG(<expr>, <expr>)
- D. HASH_AGG_COMPARE(*)
- E. HASH_COMPARE(*)
Answer : BD
Question 4
Which stages support external tables?
- A. Internal stages only; within a single Snowflake account
- B. Internal stages only; from any Snowflake account in the organization
- C. External stages only; from any region, and any cloud provider
- D. External stages only; only on the same region and cloud provider as the Snowflake account
Answer : C
Question 5
A Data Engineer wants to check the status of a pipe named my_pipe. The pipe is inside a database named test and a schema named Extract (case-sensitive).
Which query will provide the status of the pipe?
- A. SELECT SYSTEM$PIPE_STATUS("test.'extract'.my_pipe");
- B. SELECT SYSTEM$PIPE_STATUS('test."Extract".my_pipe');
- C. SELECT * FROM SYSTEM$PIPE_STATUS('test."Extract".my_pipe');
- D. SELECT * FROM SYSTEM$PIPE_STATUS("test.'extract'.my_pipe");
Answer : B
Question 6
Company A and Company B both have Snowflake accounts. Company A's account is hosted on a different cloud provider and region than Company B's account. Companies A and B are not in the same Snowflake organization.
How can Company A share data with Company B? (Choose two.)
- A. Create a share within Company A's account and add Company B's account as a recipient of that share.
- B. Create a share within Company A's account, and create a reader account that is a recipient of the share. Grant Company B access to the reader account.
- C. Use database replication to replicate Company A's data into Company B's account. Create a share within Company B's account and grant users within Company B's account access to the share.
- D. Create a new account within Company A's organization in the same cloud provider and region as Company B's account. Use database replication to replicate Company A's data to the new account. Create a share within the new account, and add Company B's account as a recipient of that share.
- E. Create a separate database within Company A's account to contain only those data sets they wish to share with Company B. Create a share within Company A's account and add all the objects within this separate database to the share. Add Company B's account as a recipient of the share.
Answer : AB
Question 7
A Data Engineer is trying to load the following rows from a CSV file into a table in Snowflake with the following structure:
The engineer is using the following COPY INTO statement:
However, the following error is received:
Number of columns in file (6) does not match that of the corresponding table (3), use file format option error_on_column_count_mismatch=false to ignore this error File 'address.csv.gz', line 3, character 1 Row 1 starts at line 2, column "STGCUSTOMER"[6] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option.
Which file format option should be used to resolve the error and successfully load all the data into the table?
- A. ESCAPE_UNENCLOSED FIELD = '\\'
- B. ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
- C. FIELD_DELIMITER = ','
- D. FIELD_OPTIONALLY_ENCLOSED_BY = '"'
Answer : D
Question 8
A Data Engineer is working on a continuous data pipeline which receives data from Amazon Kinesis Firehose and loads the data into a staging table which will later be used in the data transformation process. The average file size is 300-500 MB.
The Engineer needs to ensure that Snowpipe is performant while minimizing costs.
How can this be achieved?
- A. Increase the size of the virtual warehouse used by Snowpipe.
- B. Split the files before loading them and set the SIZE_LIMIT option to 250 MB.
- C. Change the file compression size and increase the frequency of the Snowpipe loads.
- D. Decrease the buffer size to trigger delivery of files sized between 100 to 250 MB in Kinesis Firehose.
Answer : D
Question 9
Within a Snowflake account. permissions have been defined with custom roles and role hierarchies.
To set up column-level masking using a role in the hierarchy of the current user, what command would be used?
- A. CURRENT_ROLE
- B. INVOKER_ROLE
- C. IS_ROLE_IN_SESSION
- D. IS_GRANTED_TO_INVOKER_ROLE
Answer : D
Question 10
Assuming a Data Engineer has all appropriate privileges and context, which statements would be used to assess whether the User-Defined Function (UDF), MYDATABASE.SALES.REVENUE_BY_REGION, exists and is secure? (Choose two.)
- A. SHOW USER FUNCTIONS LIKE 'REVENUE_BY_REGION' IN SCHEMA SALES;
- B. SELECT IS_SECURE FROM SNOWFLAKE.INFORMATION_SCHEMA.FUNCTIONS WHERE FUNCTION_SCHEMA = 'SALES' AND FUNCTION_NAME = 'REVENUE_BY_REGION';
- C. SELECT IS_SECURE FROM INFORMATION_SCHEMA.FUNCTIONS WHERE FUNCTION_SCHEMA = 'SALES' AND FUNCTION_NAME = 'REVENUE_BY_REGION';
- D. SHOW EXTERNAL FUNCTIONS LIKE 'REVENUE_BY_REGION' IN SCHEMA SALES;
- E. SHOW SECURE FUNCTIONS LIKE 'REVENUE_BY_REGION' IN SCHEMA SALES;
Answer : AC
Question 11
A Data Engineer has written a stored procedure that will run with caller's rights. The Engineer has granted ROLEA the right to use this stored procedure.
What is a characteristic of the stored procedure being called using ROLEA?
- A. The stored procedure must run with caller's rights; it cannot be converted later to run with owner's rights.
- B. If the stored procedure accesses an object that ROLEA does not have access to, the stored procedure will fail.
- C. The stored procedure will run in the context (database and schema) where the owner created the stored procedure.
- D. ROLEA will not be able to see the source code for the stored procedure, even though the role has usage privileges on the stored procedure.
Answer : B
Question 12
What is a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake?
- A. All types of JavaScript variables can be bound.
- B. All Snowflake first-class objects can be bound.
- C. Only JavaScript variables of type number, string, and SfDate can be bound.
- D. Users are restricted from binding JavaScript variables because they create SQL injection attack vulnerabilities.
Answer : C
Question 13
Which use case would be BEST suited for the search optimization service?
- A. Analysts who need to perform aggregates over high-cardinality columns.
- B. Business users who need fast response times using highly selective filters.
- C. Data Scientists who seek specific JOIN statements with large volumes of data.
- D. Data Engineers who create clustered tables with frequent reads against clustering keys.
Answer : B
Question 14
What is a characteristic of the operations of streams in Snowflake?
- A. Whenever a stream is queried, the offset is automatically advanced.
- B. When a stream is used to update a target table, the offset is advanced to the current time.
- C. Querying a stream returns all change records and table rows from the current offset to the current time.
- D. Each committed and uncommitted transaction on the source table automatically puts a change record in the stream.
Answer : B
Question 15
At what isolation level are Snowflake streams?
- A. Snapshot
- B. Repeatable read
- C. Read committed
- D. Read uncommitted
Answer : B